﻿Imports Telerik.Windows.Controls
Imports Telerik.Windows.Data
Imports Global_Visas_Business_Layer
Imports System.ComponentModel
Imports System.Data
Imports System.Text
Partial Public Class frmAdminSkilledOccupations

    Private Sub frmAdminSkilledOccupations_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        GeneralTasks.ChangeTheme(Me)
        GeneralTasks.EnumerateandFill(Me.LogicalChildren)
        FillDestinations()
        FillIndustries()
        Me.Btn_DeleteOcc.Visibility = Windows.Visibility.Hidden
        Me.Btn_EditOcc.Visibility = Windows.Visibility.Hidden
        Me.Btn_Inactive.Visibility = Windows.Visibility.Hidden
        Me.Btn_Cancel.Visibility = Windows.Visibility.Hidden
        Me.Btn_AddOcc.Visibility = Windows.Visibility.Hidden
    End Sub

    Private Sub FillIndustries()
        Dim industryList As DataTable = Admin.IndustryList
        With Me.Combo_Industry
            .SelectedValuePath = "Industry_ID"
            .DisplayMemberPath = "Industry"
            .ItemsSource = CType(industryList, IListSource).GetList
        End With
        With Me.Combo_Industry_Old
            .SelectedValuePath = "Industry_ID"
            .DisplayMemberPath = "Industry"
            .ItemsSource = CType(industryList, IListSource).GetList
        End With
    End Sub

    Private Sub FillDestinations()
        Dim SQL As New StringBuilder
        With SQL
            .Append("select Country_ID,Country_Name from L_country  ")
            .Append(" where Country_Name like 'australia%' or ")
            .Append(" Country_Name like 'south af%' or ")
            .Append("    Country_Name like 'canada%' or ")
            .Append(" Country_Name like 'india' or ")
            .Append(" Country_Name like 'new zealand%' or ")
            .Append(" Country_Name like 'USA%' or ")
            .Append(" Country_Name like 'United Kingdom'")
        End With
        Dim destinationList As DataTable = CommonDataFunctions.GetSQLDatatable(SQL.ToString())
        With Me.Combo_Destination
            .SelectedValuePath = "Country_ID"
            .DisplayMemberPath = "Country_Name"
            .ItemsSource = CType(destinationList, IListSource).GetList
        End With
        Call Switch_OccControls(0)
    End Sub

    Private Sub Combo_Destination_SelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles Combo_Destination.SelectionChanged

        Me.List_Categories.ItemsSource = Nothing
        Call SQL_Categories()

        'Me.List_Occupations.RowSource = string.Empty
        'Me.List_Occupations.Requery()

        'Today do
        Call Switch_CatControls(0)

        'Me.btn_EditOcc.Enabled = False
        'Me.Btn_DeleteOcc.Enabled = False
        'Me.Btn_Inactive.Enabled = False


    End Sub

    Private Sub SQL_Categories()
        Dim StrSQL As String

        StrSQL = "SELECT SkilledCategory_ID, SkilledCategory_Name"
        StrSQL = StrSQL & " FROM L_SkilledCategories"
        StrSQL = StrSQL & " WHERE SkilledCategory_CountryID = " & Me.Combo_Destination.SelectedValue
        StrSQL = StrSQL & " ORDER BY SkilledCategory_Name"

        Dim categoryList As DataTable = CommonDataFunctions.GetSQLDatatable(StrSQL.ToString())
        With Me.List_Categories
            .SelectedValuePath = "SkilledCategory_ID"
            .DisplayMemberPath = "SkilledCategory_Name"
            .ItemsSource = CType(categoryList, IListSource).GetList
        End With

        'StrSQL = "SELECT AssessingAuth_ID, AssessingAuth_Name"
        'StrSQL = StrSQL & " FROM L_AssessingAuths"
        'StrSQL = StrSQL & " WHERE AssessingAuth_CountryID = " & Me.Combo_Destination
        'StrSQL = StrSQL & " ORDER BY AssessingAuth_Name"

        'Call GblFunc_Populate_List(StrSQL, "SkillledOcc_AssAuths", Form_FMA_SkilledOcc.Combo_AssAuth)
      
    End Sub

    Private Sub Switch_CatControls(ByVal OnOff As Integer)

        If OnOff = 1 Then
            Me.Btn_AddOcc.Visibility = Windows.Visibility.Visible
            Me.Btn_EditOcc.Visibility = Windows.Visibility.Visible
            Me.Btn_DeleteOcc.Visibility = Windows.Visibility.Visible
            Me.Btn_Inactive.Visibility = Windows.Visibility.Visible
        Else
            Me.Btn_AddOcc.Visibility = Windows.Visibility.Hidden
            Me.Btn_EditOcc.Visibility = Windows.Visibility.Hidden
            Me.Btn_DeleteOcc.Visibility = Windows.Visibility.Hidden
            Me.Btn_Inactive.Visibility = Windows.Visibility.Hidden
        End If

        Call Switch_OccControls(0)

    End Sub

    Private Sub Switch_OccControls(ByVal OnOff As Integer)

        Dim StrSQL As String, Rst As DataTable

        '0 = hide controls
        '1 = set controls to visible and blank
        '2 = set controls to visible and display selected occ

        Select Case OnOff

            Case 0
                Me.List_Categories.IsEnabled = True
                Me.List_Occupations.IsEnabled = False
                Me.List_Occupations.Focus()

                Me.Chk_MODL.Visibility = Windows.Visibility.Hidden
                Me.Chk_STNI.Visibility = Windows.Visibility.Hidden

                Me.List_AvailAusEdu.Visibility = Windows.Visibility.Hidden
                Me.List_UsedAusEdu.Visibility = Windows.Visibility.Hidden
                'Me.Lbl_AusAvailable.Visibility = Windows.Visibility.Hidden
                'Me.Lbl_AusSelected.Visibility = Windows.Visibility.Hidden
                Me.Lbl_Austitle.Visibility = Windows.Visibility.Hidden
                Me.Btn_AddAll.Visibility = Windows.Visibility.Hidden
                Me.Btn_AddOne.Visibility = Windows.Visibility.Hidden
                Me.Btn_RemoveAll.Visibility = Windows.Visibility.Hidden
                Me.Btn_RemoveOne.Visibility = Windows.Visibility.Hidden

                ' Me.Lbl_AssAvail.Visibility = Windows.Visibility.Hidden
                Me.Lbl_Assessing.Visibility = Windows.Visibility.Hidden
                'Me.Lbl_AssUsed.Visibility = Windows.Visibility.Hidden
                Me.List_AvailableAssessing.Visibility = Windows.Visibility.Hidden
                Me.List_SelectedAssessing.Visibility = Windows.Visibility.Hidden
                Me.Btn_AddOneAss.Visibility = Windows.Visibility.Hidden
                Me.Btn_RemoveOneAss.Visibility = Windows.Visibility.Hidden
                Me.lblOccupation.Visibility = Windows.Visibility.Hidden
                Me.lblPoints.Visibility = Windows.Visibility.Hidden
                Me.lblASCO.Visibility = Windows.Visibility.Hidden
                Me.Industry_Label.Visibility = Windows.Visibility.Hidden

                Me.Txt_Asco.Visibility = Windows.Visibility.Hidden
                Me.Txt_Occupation.Visibility = Windows.Visibility.Hidden
                Me.Txt_Points.Visibility = Windows.Visibility.Hidden
                Me.Combo_Industry.Visibility = Windows.Visibility.Hidden

                Me.Btn_Save.Visibility = Windows.Visibility.Hidden
                Me.Btn_Cancel.Visibility = Windows.Visibility.Hidden

                Me.Btn_AddNewCat.IsEnabled = True
                Me.Btn_AddOcc.IsEnabled = True

                If Me.List_Occupations.Items.Count <> 0 Then
                    Me.Btn_EditOcc.IsEnabled = True
                    Me.Btn_DeleteOcc.IsEnabled = True
                    Me.Btn_Inactive.IsEnabled = True
                End If

                Me.btnClose.IsEnabled = True

            Case 1
                Me.Txt_Occupation.Text = String.Empty
                Me.Txt_Occupation.Visibility = Windows.Visibility.Visible
                Me.Txt_Occupation.IsEnabled = True
                Me.lblOccupation.Visibility = Windows.Visibility.Visible
                Me.Txt_Points.Text = 0
                Me.Txt_Points.IsEnabled = True
                Me.Txt_Points.Visibility = Windows.Visibility.Visible
                Me.lblPoints.Visibility = Windows.Visibility.Visible
                Me.Combo_Industry.Text = 0
                Me.Combo_Industry.Visibility = Windows.Visibility.Visible
                Me.Combo_Industry.IsEnabled = True
                Me.Industry_Label.Visibility = Windows.Visibility.Visible
                If Me.Combo_Destination.SelectedValue = 2 Then
                    'Aus
                    Me.Txt_Asco.Text = String.Empty
                    Me.Txt_Asco.Visibility = Windows.Visibility.Visible
                    Me.Txt_Asco.IsEnabled = False

                    Me.Chk_STNI.IsChecked = False
                    Me.Chk_STNI.Visibility = Windows.Visibility.Visible
                    Me.Chk_STNI.IsEnabled = False

                    Me.Chk_MODL.IsChecked = False
                    Me.Chk_MODL.Visibility = Windows.Visibility.Visible
                    Me.Chk_MODL.IsEnabled = False
                End If

                Me.Btn_Save.Content = "Add"
                Me.Btn_Save.Visibility = Windows.Visibility.Visible

                Me.Btn_Cancel.Visibility = Windows.Visibility.Visible

                Me.List_Categories.IsEnabled = True
                Me.List_Occupations.IsEnabled = True

                Me.btnClose.IsEnabled = False
                Me.Btn_AddNewCat.IsEnabled = False
                Me.Txt_Occupation.Focus()
                Me.Btn_AddOcc.IsEnabled = False
                Me.Btn_EditOcc.IsEnabled = False
                Me.Btn_DeleteOcc.IsEnabled = False
                Me.Btn_Inactive.IsEnabled = False

                Me.Lbl_Austitle.Visibility = Windows.Visibility.Hidden
                'Me.Lbl_AusAvailable.Visible = False
                'Me.Lbl_AusSelected.Visible = False
                Me.List_AvailAusEdu.Visibility = Windows.Visibility.Hidden
                Me.List_UsedAusEdu.Visibility = Windows.Visibility.Hidden
                Me.Btn_AddAll.Visibility = Windows.Visibility.Hidden
                Me.Btn_AddOne.Visibility = Windows.Visibility.Hidden
                Me.Btn_RemoveAll.Visibility = Windows.Visibility.Hidden
                Me.Btn_RemoveOne.Visibility = Windows.Visibility.Hidden

                'Me.Lbl_AssAvail.Visibility = Windows.Visibility.Hidden
                Me.Lbl_Assessing.Visibility = Windows.Visibility.Hidden
                ' Me.Lbl_AssUsed.Visibility = Windows.Visibility.Hidden
                Me.List_AvailableAssessing.Visibility = Windows.Visibility.Hidden
                Me.List_SelectedAssessing.Visibility = Windows.Visibility.Hidden
                Me.Btn_AddOneAss.Visibility = Windows.Visibility.Hidden
                Me.Btn_RemoveOneAss.Visibility = Windows.Visibility.Hidden


            Case 2

                Me.Btn_EditOcc.IsEnabled = True
                Me.Btn_DeleteOcc.IsEnabled = True
                Me.Btn_Inactive.IsEnabled = True

                Me.Txt_Occupation.Visibility = Windows.Visibility.Visible
                Me.Txt_Occupation.IsEnabled = True

                Me.Txt_Points.Visibility = Windows.Visibility.Visible
                Me.Txt_Points.IsEnabled = True

                Me.Combo_Industry.Visibility = Windows.Visibility.Visible
                Me.Combo_Industry.IsEnabled = True

                Me.Txt_Asco.IsEnabled = True
                Me.Chk_STNI.IsEnabled = True
                Me.Chk_MODL.IsEnabled = True

                'If Aussie then setup edu controls
                If Me.Combo_Destination.SelectedValue = 2 Then
                    Call SQL_AusEdu()
                    Me.List_AvailAusEdu.Visibility = Windows.Visibility.Visible
                    Me.List_UsedAusEdu.Visibility = Windows.Visibility.Visible
                    'Me.Lbl_AusAvailable.Visibility = Windows.Visibility.Visible
                    ' Me.Lbl_AusSelected.Visibility = Windows.Visibility.Visible
                    Me.Lbl_Austitle.Visibility = Windows.Visibility.Visible
                    Me.Btn_AddAll.Visibility = Windows.Visibility.Visible
                    Me.Btn_AddOne.Visibility = Windows.Visibility.Visible
                    Me.Btn_RemoveAll.Visibility = Windows.Visibility.Visible
                    Me.Btn_RemoveOne.Visibility = Windows.Visibility.Visible

                    Me.Txt_Asco.Visibility = Windows.Visibility.Visible
                    Me.Chk_STNI.Visibility = Windows.Visibility.Visible
                    Me.Chk_MODL.Visibility = Windows.Visibility.Visible

                Else
                    Me.List_AvailAusEdu.Visibility = Windows.Visibility.Hidden
                    Me.List_UsedAusEdu.Visibility = Windows.Visibility.Hidden
                    'Me.Lbl_AusAvailable.Visibility = Windows.Visibility.Visible
                    'Me.Lbl_AusSelected.Visibility = Windows.Visibility.Visible
                    Me.Lbl_Austitle.Visibility = Windows.Visibility.Hidden
                    Me.Btn_AddAll.Visibility = Windows.Visibility.Hidden
                    Me.Btn_AddOne.Visibility = Windows.Visibility.Hidden
                    Me.Btn_RemoveAll.Visibility = Windows.Visibility.Hidden
                    Me.Btn_RemoveOne.Visibility = Windows.Visibility.Hidden

                    Me.Txt_Asco.Visibility = Windows.Visibility.Hidden
                    Me.Chk_STNI.Visibility = Windows.Visibility.Hidden
                    Me.Chk_MODL.Visibility = Windows.Visibility.Hidden
                    'Me.List_AvailableAssessing.Visibility = Windows.Visibility.Hidden
                    'Me.Lbl_Assessing.Visibility = Windows.Visibility.Hidden
                    'Me.List_SelectedAssessing.Visibility = Windows.Visibility.Hidden
                End If

                Me.Btn_Save.Visibility = Windows.Visibility.Hidden
                Me.Btn_Cancel.Visibility = Windows.Visibility.Hidden
                Dim selectedOccupation As DataRow = List_Occupations.SelectedItem
                StrSQL = "Select SkilledOcc_Title, SkilledOcc_ASCO, SkilledOcc_points, SkilledOcc_AuthID, SkilledOcc_STNI, SkilledOCC_MODL, SkilledOcc_IndustryID from L_SkilledOcc where SkilledOcc_ID = " & selectedOccupation(0)
                Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)

                Me.Txt_Occupation.Text = Rst.Rows(0)("SkilledOcc_Title")
                Me.Txt_Occ_Old.Text = Rst.Rows(0)("SkilledOcc_Title")
                Me.Txt_Asco.Text = Rst.Rows(0)("SkilledOcc_ASCO")
                Me.Txt_Asco_Old.Text = Rst.Rows(0)("SkilledOcc_ASCO")
                Me.Txt_Points.Text = Rst.Rows(0)("SkilledOCC_Points")
                Me.Txt_Points_Old.Text = Rst.Rows(0)("SkilledOCC_Points")
                Me.Combo_Industry.SelectedValue = Rst.Rows(0)("SkilledOcc_IndustryID")
                Me.Combo_Industry_Old.SelectedValue = Rst.Rows(0)("SkilledOcc_IndustryID")

                If Rst.Rows(0)("SkilledOcc_STNI") = 1 Then
                    Me.Chk_STNI.IsChecked = True
                    Me.Chk_STNI_Old.IsChecked = True
                Else
                    Me.Chk_STNI.IsChecked = False
                    Me.Chk_STNI_Old.IsChecked = False
                End If

                If Rst.Rows(0)("SkilledOcc_MODL") = 1 Then
                    Me.Chk_MODL.IsChecked = True
                    Me.Chk_Modl_Old.IsChecked = True
                Else
                    Me.Chk_MODL.IsChecked = False
                    Me.Chk_Modl_Old.IsChecked = False
                End If
                Rst.Dispose()

                StrSQL = "SELECT AssessingAuth_ID"
                StrSQL = StrSQL & " FROM L_AssessingAuths"
                StrSQL = StrSQL & " WHERE (AssessingAuth_CountryID = 2)"

                Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)

                If Rst.Rows.Count > 0 Then
                    Rst.Dispose()

                    'Me.Lbl_AssAvail.Visibility = Windows.Visibility.Visible
                    Me.Lbl_Assessing.Visibility = Windows.Visibility.Visible
                    'Me.Lbl_AssUsed.Visibility = Windows.Visibility.Visible
                    Me.List_AvailableAssessing.Visibility = Windows.Visibility.Visible
                    Me.List_SelectedAssessing.Visibility = Windows.Visibility.Visible
                    Me.Btn_AddOneAss.Visibility = Windows.Visibility.Visible
                    Me.Btn_RemoveOneAss.Visibility = Windows.Visibility.Visible

                    Call SQL_Assessing()
                Else
                    Rst.Dispose()

                    'Me.Lbl_AssAvail.Visibility = Windows.Visibility.Hidden
                    Me.Lbl_Assessing.Visibility = Windows.Visibility.Hidden
                    'Me.Lbl_AssUsed.Visibility = Windows.Visibility.Hidden
                    Me.List_AvailableAssessing.Visibility = Windows.Visibility.Hidden
                    Me.List_SelectedAssessing.Visibility = Windows.Visibility.Hidden
                    Me.Btn_AddOneAss.Visibility = Windows.Visibility.Hidden
                    Me.Btn_RemoveOneAss.Visibility = Windows.Visibility.Hidden
                End If

        End Select

    End Sub

    Private Sub SQL_AusEdu()

        Dim StrSQL As String, Rst As DataTable, IntRec As Integer
        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        StrSQL = "SELECT L_SkilledOccEduLink.SkilledOccEduLink_ID, L_AusEduCat.AusEduCat_Name as Selected"
        StrSQL = StrSQL & " FROM L_SkilledOccEduLink INNER JOIN"
        StrSQL = StrSQL & " L_AusEduCat ON L_SkilledOccEduLink.SkilledOccEduLink_EduID = L_AusEduCat.AusEduCat_ID"
        StrSQL = StrSQL & " WHERE (L_SkilledOccEduLink.SkilledOccEduLink_SkilledOccID = " & selectedOccupation(0) & ")"

        'Call GPL(StrSQL, "SkilledOcc_EduLinks", Form_FMA_SkilledOcc.List_UsedAusEdu)
        Dim eduLinks As DataTable = CommonDataFunctions.GetSQLDatatable(StrSQL)
        Me.List_UsedAusEdu.ItemsSource = eduLinks
        Me.List_UsedAusEdu.Columns(0).IsVisible = False
        'Me.List_UsedAusEdu = Null

        Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)
        IntRec = Rst.Rows.Count
        Rst.Dispose()

        StrSQL = "SELECT AusEduCat_ID, AusEduCat_Name as Available"
        StrSQL = StrSQL & " FROM L_AusEduCat"

        If IntRec > 0 Then

            StrSQL = StrSQL & " WHERE AusEduCat_ID not IN ("
            StrSQL = StrSQL & " SELECT SkilledOccEduLink_EduID"
            StrSQL = StrSQL & " FROM L_SkilledOccEduLink"
            StrSQL = StrSQL & " WHERE (SkilledOccEduLink_SkilledOccID = " & selectedOccupation(0) & ")"
            StrSQL = StrSQL & " )"
        End If

        'Call GPL(StrSQL, "SkilledOcc_AvailableEdu", Form_FMA_SkilledOcc.List_AvailAusEdu)
        Dim eduAvailable As DataTable = CommonDataFunctions.GetSQLDatatable(StrSQL)
        Me.List_AvailAusEdu.ItemsSource = eduAvailable
        Me.List_AvailAusEdu.Columns(0).IsVisible = False
        'Me.List_AvailAusEdu = Null

        ' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
        Exit Sub

    End Sub

    Private Sub SQL_Assessing()

        Dim StrSQL As String, Rst As DataTable, IntRec As Integer
        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        StrSQL = "SELECT L_SkilledOccAssAuth.SkilledOccAssAuth_ID, L_AssessingAuths.AssessingAuth_Name as Selected"
        StrSQL = StrSQL & " FROM L_SkilledOccAssAuth INNER JOIN"
        StrSQL = StrSQL & " L_AssessingAuths ON L_SkilledOccAssAuth.SkilledOccAssAuth_AssAuthID = L_AssessingAuths.AssessingAuth_ID"
        StrSQL = StrSQL & " WHERE L_SkilledOccAssAuth.SkilledOccAssAuth_SkilledOccID = " & selectedOccupation(0)

        'Call GPL(StrSQL, "SkilledOcc_AssLinks", Form_FMA_SkilledOcc.List_SelectedAssessing)
        Dim assLinksList As DataTable = CommonDataFunctions.GetSQLDatatable(StrSQL)
        Me.List_SelectedAssessing.ItemsSource = assLinksList
        Me.List_SelectedAssessing.Columns(0).IsVisible = False
        Me.List_SelectedAssessing.Columns(1).Width = New GridLength(180)
        'Me.List_SelectedAssessing = Null

        Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)
        IntRec = Rst.Rows.Count
        Rst.Dispose()

        StrSQL = "SELECT AssessingAuth_ID, AssessingAuth_Name as Available"
        StrSQL = StrSQL & " FROM L_AssessingAuths"

        If IntRec > 0 Then

            StrSQL = StrSQL & " WHERE AssessingAuth_ID not IN ("
            StrSQL = StrSQL & " SELECT SkilledOccAssAuth_AssAuthID"
            StrSQL = StrSQL & " FROM  L_SkilledOccAssAuth"
            StrSQL = StrSQL & " WHERE (SkilledOccAssAuth_SkilledOccID = " & selectedOccupation(0) & ")"
            StrSQL = StrSQL & " )"
            StrSQL = StrSQL & " AND (AssessingAuth_CountryID = " & Me.Combo_Destination.SelectedValue & ")"
        Else
            StrSQL = StrSQL & " WHERE AssessingAuth_CountryID = " & Me.Combo_Destination.SelectedValue
        End If


        'Call GPL(StrSQL, "SkilledOcc_AvailableAss", Form_FMA_SkilledOcc.List_AvailableAssessing)
        Dim assAvailableList As DataTable = CommonDataFunctions.GetSQLDatatable(StrSQL)
        Me.List_AvailableAssessing.ItemsSource = assAvailableList
        Me.List_AvailableAssessing.Columns(0).IsVisible = False
        Me.List_AvailableAssessing.Columns(1).Width = New GridLength(180)
        'Me.List_AvailableAssessing = Null
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub List_Categories_SelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles List_Categories.SelectionChanged

        If Me.List_Categories.Items.Count = 0 Then Exit Sub

        Call SQL_Occupations()
        Call Switch_CatControls(1)

        Me.Btn_EditOcc.IsEnabled = False
        Me.Btn_DeleteOcc.IsEnabled = False
        Me.Btn_Inactive.IsEnabled = False
        Me.List_Occupations.IsEnabled = True

    End Sub

    Private Sub SQL_Occupations()

        Dim StrSQL As String

        StrSQL = "SELECT L_SkilledOcc.SkilledOcc_ID, L_SkilledOcc.SkilledOcc_Title as Occupation,L_Industry.Industry_Name as Industry, L_SkilledOcc.SkilledOcc_Points as Points "
        StrSQL = StrSQL & " , Case L_SkilledOcc.SkilledOcc_Inactive when 1 then 'Yes' else '' end as Inactive"
        StrSQL = StrSQL & " FROM L_SkilledOcc LEFT OUTER JOIN"
        StrSQL = StrSQL & " L_Industry ON L_SkilledOcc.SkilledOcc_IndustryID = L_Industry.Industry_ID"
        StrSQL = StrSQL & " WHERE (L_SkilledOcc.SkilledOcc_CategoryID = " & Me.List_Categories.SelectedValue & ")"
        If Me.Chk_Inactive.IsChecked = False Then
            StrSQL = StrSQL & " AND L_SkilledOcc.SkilledOcc_Inactive = 0"
        End If
        StrSQL = StrSQL & " ORDER BY L_SkilledOcc.SkilledOcc_Title"

        'Call GblFunc_Populate_List(StrSQL, "SkilledOccupations_OccList", Form_FMA_SkilledOcc.List_Occupations)
        Dim occupationList As DataTable = CommonDataFunctions.GetSQLDatatable(StrSQL)
        Me.List_Occupations.ItemsSource = occupationList
        Me.List_Occupations.Columns(0).IsVisible = False
        Me.List_Occupations.Columns(1).Width = New GridLength(250)
        Me.List_Occupations.Columns(3).Width = New GridLength(80)
        'Me.List_Occupations = Null
        Me.List_Occupations.IsEnabled = True
        Me.Btn_DeleteOcc.IsEnabled = False
        Me.Btn_EditOcc.IsEnabled = False
        Me.Btn_Inactive.IsEnabled = False
 
    End Sub

    Private Sub Btn_AddNewCat_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_AddNewCat.Click

        Dim StrSQL As String, StrMess As String, IntCat As Integer, StrCat As String, Rst As DataTable
        If Combo_Destination.SelectedIndex = -1 Then
            MsgBox("Please select the destination first in order to add new category .")
            Exit Sub
        End If
        StrCat = InputBox("New Category name", "Add Category")
        If StrCat = String.Empty Then
            Call MsgBox("Action cancelled")
            Exit Sub
        End If

        StrSQL = "SELECT SkilledCategory_ID FROM L_SkilledCategories"
        StrSQL = StrSQL & " WHERE SkilledCategory_Name = '" & StrCat.Trim & "'"
        StrSQL = StrSQL & " AND SkilledCategory_CountryID = " & Me.Combo_Destination.SelectedValue

        Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)

        If Rst.Rows.Count > 0 Then
            IntCat = Rst.Rows(0)("SkilledCategory_ID")
            Rst.Dispose()

            Call MsgBox("That Category already exits for this destination", , "Error")
            'Me.List_Categories = IntCat
            'Call List_Categories_AfterUpdate()
            Exit Sub
        End If

        Rst.Dispose()

        StrSQL = "Insert into L_SkilledCategories (SkilledCategory_Name, SkilledCategory_CountryID) VALUES('" & StrCat & "', " & Me.Combo_Destination.SelectedValue & ")"
        If CommonDataFunctions.ExecuteSQL(StrSQL) = False Then Exit Sub

        StrSQL = "SELECT SkilledCategory_ID FROM L_SkilledCategories"
        StrSQL = StrSQL & " WHERE SkilledCategory_Name = '" & StrCat & "'"

        Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)

        If Rst.Rows.Count = 0 Then
            Rst.Dispose()
            Call MsgBox("New category not found", , "Error")
            Exit Sub
        End If

        IntCat = Rst.Rows(0)("SkilledCategory_ID")
        Rst.Dispose()

        'Me.List_Categories.Requery()
        Me.List_Categories.SelectedValue = IntCat


        StrMess = "New Skilled Occupation Category created." & vbCrLf & vbCrLf
        StrMess = StrMess & "ID: " & IntCat & vbCrLf
        StrMess = StrMess & "Name: " & StrCat & vbCrLf
        StrMess = StrMess & "Destination: " & Me.Combo_Destination.Text & vbCrLf

        MsgBox(StrMess)

    End Sub

    Private Sub Btn_AddOcc_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_AddOcc.Click
        If Me.List_Categories.SelectedIndex = -1 Then
            MsgBox("Please select the category to add New Occupation.")
            Exit Sub
        End If
        Call Switch_OccControls(1)
    End Sub

    Private Sub Btn_Save_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_Save.Click

        Dim StrSQL As String, StrMess As String, Rst As DataTable ', LngOcc As Long, IntDirty As Integer, StrOld As String
        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        If Me.Txt_Occupation.Text = String.Empty Then
            Call MsgBox("Occupation cannot be blank", , "Error")
            Me.Txt_Occupation.Focus()
            Exit Sub
        End If

        'If IsNull(Me.Txt_Asco) Then Me.Txt_Asco.Text = String.Empty

        If Me.Txt_Points.Text = String.Empty Then Me.Txt_Points.Text = 0

        If Me.Combo_Industry.SelectedIndex = -1 Or Me.Combo_Industry.Text = String.Empty Then
            Me.Combo_Industry.SelectedIndex = 0
        End If

        StrSQL = "SELECT SkilledOcc_ID"
        StrSQL = StrSQL & " FROM L_SkilledOcc"
        StrSQL = StrSQL & " WHERE (SkilledOcc_CategoryID = " & Me.List_Categories.SelectedValue & ") "
        StrSQL = StrSQL & " AND (SkilledOcc_Title = '" & Me.Txt_Occupation.Text & "')"
        If Me.Btn_Save.Content = "Save" Then
            StrSQL = StrSQL & " AND (SkilledOcc_ID <> " & selectedOccupation(0) & ")"
        End If


        Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)
        If Rst.Rows.Count > 0 Then
            Me.List_Occupations = Rst.Rows(0)("SkilledOcc_ID")
            Rst.Dispose()
            StrMess = "That occupation already exists in this category"
            Call MsgBox(StrMess, , "Error")
            Exit Sub
        End If

        Rst.Dispose()

        If Me.Btn_Save.Content = "Add" Then

            StrSQL = "Insert into L_SkilledOcc"
            StrSQL = StrSQL & " (SkilledOcc_CategoryID, SkilledOcc_Title, SkilledOcc_ASCO, SkilledOcc_IndustryID, SkilledOcc_Points, SkilledOcc_STNI, SkilledOcc_MODL)"
            StrSQL = StrSQL & " VALUES( "
            StrSQL = StrSQL & Me.List_Categories.SelectedValue & ", "
            StrSQL = StrSQL & "'" & Me.Txt_Occupation.Text & "', "
            StrSQL = StrSQL & "'" & Me.Txt_Asco.Text & "', "
            StrSQL = StrSQL & Me.Combo_Industry.SelectedValue & ", "
            StrSQL = StrSQL & Me.Txt_Points.Text & ", "
            If Me.Chk_STNI.IsChecked = True Then
                StrSQL = StrSQL & "1, "
            Else
                StrSQL = StrSQL & "0, "
            End If

            If Me.Chk_MODL.IsChecked = True Then
                StrSQL = StrSQL & "1) "
            Else
                StrSQL = StrSQL & "0) "
            End If

            If CommonDataFunctions.ExecuteSQL(StrSQL) = False Then
                MsgBox("Error occurred while inserting new skilled occupation. Please try again or contact IT.")
                Exit Sub
            Else
                SQL_Occupations()
                MsgBox("The occupation added with success.")
            End If


            'StrSQL = "SELECT SkilledOcc_ID"
            'StrSQL = StrSQL & " FROM L_SkilledOcc"
            'StrSQL = StrSQL & " WHERE (SkilledOcc_CategoryID = " & Me.List_Categories.SelectedValue & ") "
            'StrSQL = StrSQL & " AND (SkilledOcc_Title = '" & Me.Txt_Occupation.Text & "')"

            ''Me.List_Occupations.Requery()

            'Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)
            'If Rst.Rows.Count = 0 Then
            '    Rst.Dispose()
            '    Call MsgBox("New Occupation not found")
            '    Exit Sub
            'End If

            'Me.List_Occupations = Rst.Rows(0)("SkilledOcc_ID")
            Rst.Dispose()

            'StrMess = "New Skilled Occupation added to Category no. " & Me.List_Categories.SelectedValue & " (" & Me.List_Categories.Text & ")"
            'StrMess = StrMess & vbCrLf & vbCrLf & "No: " & Me.List_Occupations
            'StrMess = StrMess & vbCrLf & vbCrLf & "Name: " & Me.Txt_Occupation
            'StrMess = StrMess & vbCrLf & vbCrLf & "ASCO: " & Me.Txt_Asco
            'StrMess = StrMess & vbCrLf & vbCrLf & "Points: " & Me.Txt_Points
            'If Me.Chk_STNI = True Then
            '    StrMess = StrMess & vbCrLf & vbCrLf & "STNI: Yes"
            'Else
            '    StrMess = StrMess & vbCrLf & vbCrLf & "STNI: No"
            'End If

            'If Me.Chk_MODL = True Then
            '    StrMess = StrMess & vbCrLf & vbCrLf & "MODL: Yes"
            'Else
            '    StrMess = StrMess & vbCrLf & vbCrLf & "MODL: No"
            'End If
        Else
            'Update stuff

            '    IntDirty = 0

            StrSQL = String.Empty

            '    If Me.Txt_Occupation.Text <> Me.Txt_Occ_Old.Text Then
            '        IntDirty = 1

            '        If Me.Txt_Occ_Old.Text = String.Empty Then
            '            StrOld = "Blank"
            '        Else
            '            StrOld = Me.Txt_Occ_Old.Text
            '        End If

            StrSQL = StrSQL & "SkilledOcc_title = '" & Me.Txt_Occupation.Text & "'"
            '        StrMess = StrMess & "Occupation title changed from :" & StrOld & " to : " & RC(Me.Txt_Occupation) & vbCrLf
            '    End If

            '    If Me.Txt_Asco <> Me.Txt_Asco_Old Then
            '        IntDirty = 1

            '        If Nz(Me.Txt_Asco_Old) =string.Empty Then
            '            StrOld = "Blank"
            '        Else
            '            StrOld = Me.Txt_Asco_Old
            '        End If

            If StrSQL <> String.Empty Then StrSQL = StrSQL & ", "

            StrSQL = StrSQL & "SkilledOcc_ASCO = '" & Me.Txt_Asco.Text & "'"
            '        StrMess = StrMess & "ASCO changed from :" & StrOld & " to : " & RC(Me.Txt_Asco) & vbCrLf
            '    End If

            '    If Me.Txt_Points <> Me.Txt_Points_Old Then
            '        IntDirty = 1

            '        If Nz(Me.Txt_Points_Old) = string.Empty Then
            '            StrOld = "Blank"
            '        Else
            '            StrOld = Me.Txt_Points_Old
            '        End If

            If StrSQL <> String.Empty Then StrSQL = StrSQL & ", "

            StrSQL = StrSQL & "SkilledOcc_points = '" & Me.Txt_Points.Text & "',"
            '        StrMess = StrMess & "Points changed from :" & StrOld & " to : " & RC(Me.Txt_Points) & vbCrLf
            '    End If

            '    If Me.Combo_Industry <> Me.Combo_Industry_Old Then
            '        IntDirty = 1

            '        If Nz(Me.Combo_Industry_Old) =string.Empty Or Me.Combo_Industry_Old = 0 Or IsNull(Me.Combo_Industry_Old.Column(1)) Then
            '            StrOld = "Blank"
            '        Else
            '            StrOld = Me.Combo_Industry_Old.Column(1)
            '        End If

            '        If StrSQL <> "" Then StrSQL = StrSQL & ", "

            StrSQL = StrSQL & "SkilledOcc_industryid = " & Me.Combo_Industry.SelectedValue
            '        StrMess = "Indsutry changed from :" & StrOld & " to : "
            '        If Me.Combo_Industry = 0 Then
            '            StrMess = StrMess & "Blank" & vbCrLf
            '        Else
            '            StrMess = StrMess & Me.Combo_Industry.Column(1) & vbCrLf
            '        End If

            '    End If

            '    If Me.Chk_MODL <> Me.Chk_Modl_Old Then

            '        IntDirty = 1

            If Me.Chk_Modl_Old.IsChecked = True Then
                If StrSQL <> String.Empty Then StrSQL = StrSQL & ", "

                StrSQL = StrSQL & "SkilledOcc_modl = 0"
                'StrMess = StrMess & "MODL changed from yes to no" & vbCrLf
            Else
                If StrSQL <> "" Then StrSQL = StrSQL & ", "

                StrSQL = StrSQL & "SkilledOcc_modl = 1"
                'StrMess = StrMess & "MODL changed from no to yes" & vbCrLf
            End If
            '    End If

            '    If Me.Chk_STNI <> Me.Chk_STNI_Old Then

            '        IntDirty = 1

            If Me.Chk_STNI_Old.IsChecked = True Then

                If StrSQL <> String.Empty Then StrSQL = StrSQL & ", "

                StrSQL = StrSQL & "SkilledOcc_stni = 0"
                StrMess = StrMess & "STNI changed from yes to no" & vbCrLf
            Else
                If StrSQL <> "" Then StrSQL = StrSQL & ", "

                StrSQL = StrSQL & "SkilledOcc_stni = 1"
                StrMess = StrMess & "STNI changed from no to yes" & vbCrLf
            End If
            '    End If



            '    If IntDirty = 1 Then

            StrMess = "Skilled Occupation no. " & selectedOccupation(0) & " (" & selectedOccupation(1).ToString() & ") in Category no. " & Me.List_Categories.SelectedValue & " (" & Me.List_Categories.Text & ") edited ."

            StrSQL = "Update L_SkilledOcc Set " & StrSQL
            StrSQL = StrSQL & " WHERE SkilledOcc_ID = " & selectedOccupation(0)

            If CommonDataFunctions.ExecuteSQL(StrSQL) = True Then
                MsgBox("Updation successful." & vbCrLf & vbCrLf & StrMess)
            Else
                MsgBox("Error ocurred while updating the occupation details. Please try again or Contact IT.")
                Exit Sub
            End If
            '        Me.List_Occupations.Requery()
            '    Else
            '        If MsgBox("Nothing changed, cancel edit?", vbYesNo, "Error") = 6 Then Call Btn_Cancel_Click()
            '        Exit Sub
            '    End If
        End If

        'Call GblFunc_Add_SysEvent("Admin (Skilled Occ)", StrMess)

        Call Btn_Cancel_Click()
    End Sub

    Private Sub Btn_Cancel_Click() Handles Btn_Cancel.Click
        Me.Btn_EditOcc.IsEnabled = True
        Me.Btn_EditOcc.Focus()
        Me.Btn_Save.Visibility = Windows.Visibility.Hidden
        Me.Btn_Cancel.Visibility = Windows.Visibility.Hidden

        Me.Txt_Asco.IsEnabled = False
        Me.Txt_Occupation.IsEnabled = False
        Me.Txt_Points.IsEnabled = False
        Me.Combo_Industry.IsEnabled = False
        Me.Chk_MODL.IsEnabled = False
        Me.Chk_STNI.IsEnabled = False

        Me.List_Categories.IsEnabled = True
        'Me.List_Categories.IsEnabled = True
        Me.List_Occupations.IsEnabled = True
        'Me.List_Occupations.IsEnabled = False
        Me.Combo_Destination.IsEnabled = True
        Me.Chk_Inactive.IsEnabled = True

        Me.Btn_AddNewCat.IsEnabled = True
        Me.Btn_AddOcc.IsEnabled = True
        Me.Btn_DeleteOcc.IsEnabled = True
        Me.Btn_EditOcc.IsEnabled = True
        Me.Btn_Inactive.IsEnabled = True
        Me.btnClose.IsEnabled = True

        Me.List_AvailAusEdu.IsEnabled = True
        Me.List_UsedAusEdu.IsEnabled = True
        Me.Btn_AddAll.IsEnabled = True
        Me.Btn_AddOne.IsEnabled = True
        Me.Btn_RemoveAll.IsEnabled = True
        Me.Btn_RemoveOne.IsEnabled = True

        Me.List_AvailableAssessing.IsEnabled = True
        Me.List_SelectedAssessing.IsEnabled = True
        Me.Btn_AddOneAss.IsEnabled = True
        Me.Btn_RemoveOneAss.IsEnabled = True


        Me.SQL_Occupations()
    End Sub

    Private Sub Btn_DeleteOcc_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_DeleteOcc.Click

        Dim StrSQL As String, Rst As DataTable, StrMess As String
        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        If selectedOccupation Is Nothing Then
            MsgBox("Please select the occupation to delete.")
            Exit Sub
        End If


        StrSQL = "Select Count(Person_ID) as NoPeople from D_Person "
        StrSQL = StrSQL & " Where Person_SkilledOccID = " & selectedOccupation(0)

        Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)

        If Rst.Rows.Count > 0 Then
            If Rst.Rows(0)("NoPeople") > 0 Then
                StrMess = Rst.Rows(0)("NoPeople") & " client have that Skilled Occupation, cannot delete. Please mark as inactive instead"
                Rst.Dispose()

                Call MsgBox(StrMess, , "Error")
                Exit Sub
            End If
        End If

        If MsgBox("Are you sure you want to delete this occupation?", vbYesNo, "Confirm Delete") <> 6 Then Exit Sub

        ' StrMess = "Skilled Occ no. " & & " (" & Me.List_Occupations.Column(1) & ") in Cateogry " & Me.List_Categories & " (" & Me.List_Categories.Column(1) & ") deleted"

        StrSQL = "Delete from L_SkilledOcc where skilledocc_id = " & selectedOccupation(0)

        If CommonDataFunctions.ExecuteSQL(StrSQL) = 0 Then Exit Sub

        StrSQL = "Delete from L_SkilledOccAssAuth where skilledoccAssauth_skilledoccid = " & selectedOccupation(0)

        If CommonDataFunctions.ExecuteSQL(StrSQL) = 0 Then Exit Sub

        StrSQL = "Delete from L_SkilledOccEduLink where skilledoccedulink_skilledoccid = " & selectedOccupation(0)

        If CommonDataFunctions.ExecuteSQL(StrSQL) = 0 Then Exit Sub

        'Call GblFunc_Add_SysEvent("Admin (Skilled Occ)", StrMess)

        Me.SQL_Occupations()
        'Me.List_Occupations = Null
        Me.List_Occupations.Focus()

        Me.Btn_DeleteOcc.IsEnabled = False
        Me.Btn_EditOcc.IsEnabled = False
        Me.Btn_Inactive.IsEnabled = False

        Call Switch_OccControls(0)
        Me.List_Occupations.IsEnabled = True
    End Sub

    Private Sub List_Occupations_SelectionChanged(ByVal sender As Object, ByVal e As Telerik.Windows.Controls.SelectionChangeEventArgs) Handles List_Occupations.SelectionChanged
        Me.Btn_DeleteOcc.IsEnabled = True
        Me.Btn_EditOcc.IsEnabled = True
        Me.Btn_Inactive.IsEnabled = True
        Call Switch_OccControls(2)
        Me.Txt_Asco.Visibility = Windows.Visibility.Visible
        Me.Txt_Points.Visibility = Windows.Visibility.Visible
        Me.Combo_Industry.Visibility = Windows.Visibility.Visible
        Me.Lbl_Assessing.Visibility = Windows.Visibility.Visible
        Me.Industry_Label.Visibility = Windows.Visibility.Visible
        Me.Chk_MODL.Visibility = Windows.Visibility.Visible
        Me.Chk_STNI.Visibility = Windows.Visibility.Visible
        Me.lblASCO.Visibility = Windows.Visibility.Visible
        Me.lblOccupation.Visibility = Windows.Visibility.Visible
        Me.lblPoints.Visibility = Windows.Visibility.Visible
        Me.lblASCO.Visibility = Windows.Visibility.Visible
    End Sub

    Private Sub Btn_EditOcc_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_EditOcc.Click

        'Dim StrSQL As String, 
        Dim Rst As DataTable

        If Me.List_Occupations.SelectedItems.Count = 0 Then Exit Sub

        Call MsgBox("Any changes to an occupation will immediately be applied to all existing clients with that skilled occupation." & vbCrLf & "Ensure you do not change the meaning of the occupation when editing", , "Warning")

        Me.Btn_Save.Content = "Save"
        Me.Btn_Save.Visibility = Windows.Visibility.Visible
        Me.Btn_Cancel.Visibility = Windows.Visibility.Visible
        Me.Txt_Occupation.Focus()

        Me.Txt_Asco.IsEnabled = True
        Me.Txt_Occupation.IsEnabled = True
        Me.Txt_Points.IsEnabled = True
        Me.Combo_Industry.IsEnabled = True
        Me.Chk_MODL.IsEnabled = True
        Me.Chk_STNI.IsEnabled = True

        Me.List_Categories.IsEnabled = False
        Me.List_Occupations.IsEnabled = False
        Me.Combo_Destination.IsEnabled = False
        Me.Chk_Inactive.IsEnabled = False

        Me.Btn_AddNewCat.IsEnabled = False
        Me.Btn_AddOcc.IsEnabled = False
        Me.Btn_DeleteOcc.IsEnabled = False
        Me.Btn_EditOcc.IsEnabled = False
        Me.Btn_Inactive.IsEnabled = False

        Me.List_AvailAusEdu.IsEnabled = False
        Me.List_UsedAusEdu.IsEnabled = False
        Me.Btn_AddAll.IsEnabled = False
        Me.Btn_AddOne.IsEnabled = False
        Me.Btn_RemoveAll.IsEnabled = False
        Me.Btn_RemoveOne.IsEnabled = False

        Me.List_AvailableAssessing.IsEnabled = False
        Me.List_SelectedAssessing.IsEnabled = False
        Me.Btn_AddOneAss.IsEnabled = False
        Me.Btn_RemoveOneAss.IsEnabled = False



        Exit Sub

    End Sub

    Private Sub Btn_Inactive_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_Inactive.Click

        Dim StrSQL As String, StrMess As String
        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        If selectedOccupation Is Nothing Then
            MsgBox("Please select the occupation first.")
            Exit Sub
        End If


        'StrMess = "Skilled Occupation no. " & Me.List_Occupations & " (" & Me.List_Occupations.Column(1) & ") in category no. " & Me.List_Categories & " (" & Me.List_Categories.Column(1) & ")"
        If selectedOccupation(4) = "Yes" Then
            StrMess = StrMess & " set to active"
        Else
            StrMess = StrMess & " set to inactive"
        End If

        StrSQL = "Update L_SkilledOcc "
        StrSQL = StrSQL & " Set SkilledOcc_Inactive = Case SkilledOcc_Inactive when 0 then 1 else 0 end"
        StrSQL = StrSQL & " Where SkilledOcc_ID = " & selectedOccupation(0)

        If CommonDataFunctions.ExecuteSQL(StrSQL) = True Then
            MsgBox("Success.")
            Exit Sub
        Else
            MsgBox("Please try again or cntact IT")
            Exit Sub
        End If


        If selectedOccupation(4) = String.Empty And Me.Chk_Inactive.IsChecked = False Then
            Me.List_Occupations.SelectedItem = Nothing
            Me.List_Occupations.Focus()
            Me.Btn_DeleteOcc.IsEnabled = False
            Me.Btn_EditOcc.IsEnabled = False
            Me.Btn_Inactive.IsEnabled = False
        End If

        SQL_Occupations()


    End Sub

    Private Sub Btn_AddOneAss_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_AddOneAss.Click

        Dim StrSQL As String
        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        If selectedOccupation Is Nothing Then
            MsgBox("Please select occupation first.")
            Exit Sub
        End If

        Dim selectedvailableAssessing As DataRow = Me.List_AvailableAssessing.SelectedItem
        If selectedvailableAssessing Is Nothing Then
            Call MsgBox("Please select an assessment authority to add", , "Error")
            Me.List_AvailableAssessing.Focus()
            Exit Sub
        End If

        StrSQL = "Insert into L_SkilledOccAssAuth (SkilledOccAssAuth_SkilledOccID, SkilledOccAssAuth_AssAuthID, SkilledOccAssAuth_EnteredBy)"
        StrSQL = StrSQL & " VALUES(" & selectedOccupation(0) & ", " & selectedvailableAssessing(0) & ", '" & GlobalCRM.CurrentConName & "')"

        If CommonDataFunctions.ExecuteSQL(StrSQL) = False Then
            MsgBox("Please try again or contact IT")
            Exit Sub
        End If

        Call SQL_Assessing()
    End Sub

    Private Sub Btn_RemoveOneAss_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_RemoveOneAss.Click
        Dim StrSQL As String

        Dim selectedAssessing As DataRow = Me.List_SelectedAssessing.SelectedItem
        If selectedAssessing Is Nothing Then
            Exit Sub
        End If

        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        If selectedOccupation Is Nothing Then
            MsgBox("Please select occupation first.")
            Exit Sub
        End If

        StrSQL = "Delete from L_SkilledOccAssAuth where SkilledOccAssAuth_Id = " & selectedAssessing(0)
        If CommonDataFunctions.ExecuteSQL(StrSQL) = False Then
            MsgBox("Error occurred. Please try again or contact IT")
        End If
        Call SQL_Assessing()

    End Sub

    Private Sub Btn_AddOne_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_AddOne.Click

        Dim StrSQL As String

        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        If selectedOccupation Is Nothing Then
            MsgBox("Please select occupation first.")
            Exit Sub
        End If

        Dim selectedAvailAusEdu As DataRow = Me.List_AvailAusEdu.SelectedItem
        
        If selectedAvailAusEdu Is Nothing Then
            Call MsgBox("Please select Education category to add", , "Error")
            Me.List_AvailAusEdu.Focus()
            Exit Sub
        End If

        StrSQL = "Insert into L_SkilledOccEduLink (SkilledOccEduLink_EduID, SkilledOccEduLink_SkilledOccID, SkilledOccEduLink_EnteredBy)"
        StrSQL = StrSQL & "VALUES(" & selectedAvailAusEdu(0) & ", " & selectedOccupation(0) & ",'" & GlobalCRM.CurrentConName & "')"

        If CommonDataFunctions.ExecuteSQL(StrSQL) = False Then
            Exit Sub
        End If

        Call SQL_AusEdu()

    End Sub

    Private Sub Btn_RemoveOne_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_RemoveOne.Click
        Dim StrSQL As String

        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        If selectedOccupation Is Nothing Then
            MsgBox("Please select occupation first.")
            Exit Sub
        End If

        Dim selectedUsedAusEdu As DataRow = Me.List_UsedAusEdu.SelectedItem
      
        If selectedUsedAusEdu Is Nothing Then
            Call MsgBox("Please select Education category to remove", , "Error")
            Me.List_AvailAusEdu.Focus()
            Exit Sub
        End If

        'StrMess = "Aus Edu category " & Me.List_UsedAusEdu & ", removed from Skilled Occ No. " & Me.List_Occupations & "(" & Me.List_Occupations.Column(1) & ")"

        StrSQL = "delete from L_SkilledOccEduLink where skilledoccedulink_id = " & selectedUsedAusEdu(0)

        If CommonDataFunctions.ExecuteSQL(StrSQL) = False Then
            MsgBox("Please try again or Contact IT")
            Exit Sub
        End If
        Call SQL_AusEdu()

    End Sub

    Private Sub Btn_AddAll_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Btn_AddAll.Click

        Dim StrSQL As String, Rst As DataTable, IntRec As Integer

        Dim selectedOccupation As DataRow = Me.List_Occupations.SelectedItem
        If selectedOccupation Is Nothing Then
            MsgBox("Please select occupation first.")
            Exit Sub
        End If


        StrSQL = "SELECT L_SkilledOccEduLink.SkilledOccEduLink_ID"
        StrSQL = StrSQL & " FROM L_SkilledOccEduLink"
        StrSQL = StrSQL & " WHERE (L_SkilledOccEduLink.SkilledOccEduLink_SkilledOccID = " & selectedOccupation(0) & ")"

        Rst = CommonDataFunctions.GetSQLDatatable(StrSQL)
        IntRec = Rst.Rows.Count
        Rst.Dispose()

        StrSQL = "Insert into L_SkilledOccEduLink (SkilledOccEduLink_EduID, SkilledOccEduLink_SkilledOccID, SkilledOccEduLink_EnteredBy)"
        StrSQL = StrSQL & "SELECT AusEduCat_ID, " & selectedOccupation(0) & " as Occ, '" & GlobalCRM.CurrentConName & "' as Con"
        StrSQL = StrSQL & " FROM L_AusEduCat"

        If IntRec > 0 Then

            StrSQL = StrSQL & " WHERE AusEduCat_ID not IN ("
            StrSQL = StrSQL & " SELECT SkilledOccEduLink_EduID"
            StrSQL = StrSQL & " FROM L_SkilledOccEduLink"
            StrSQL = StrSQL & " WHERE (SkilledOccEduLink_SkilledOccID = " & selectedOccupation(0) & ")"
            StrSQL = StrSQL & " )"
        End If

        If CommonDataFunctions.ExecuteSQL(StrSQL) = False Then Exit Sub

        Call SQL_AusEdu()
    End Sub

    Private Sub Chk_Inactive_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Chk_Inactive.Click
        SQL_Occupations()
    End Sub

End Class
